package com.maimang.platform.crm.service.impl;

import cn.hutool.core.date.DateTime;
import cn.hutool.core.date.DateUtil;
import cn.hutool.core.util.PinyinUtil;
import com.maimang.platform.common.entity.SysDept;
import com.maimang.platform.common.entity.SysDeptTree;
import com.maimang.platform.common.entity.SysUser;
import com.maimang.platform.common.mapper.SysDeptMapper;
import com.maimang.platform.common.mapper.SysUserDeptMapper;
import com.maimang.platform.common.mapper.SysUserMapper;
import com.maimang.platform.common.utils.ArithUtil;
import com.maimang.platform.common.utils.DateForEachUtil;
import com.maimang.platform.common.utils.DateUtils;
import com.maimang.platform.common.utils.excel.TitleEntity;
import com.maimang.platform.crm.config.SheetLabel;
import com.maimang.platform.crm.entity.CrmCustomer;
import com.maimang.platform.crm.entity.CrmWorkRecord;
import com.maimang.platform.crm.mapper.CrmCustomerMapper;
import com.maimang.platform.crm.mapper.CrmWorkRecordMapper;
import com.maimang.platform.crm.service.WorkExportService;
import com.maimang.platform.crm.util.TreeHelper;
import com.maimang.platform.lang.ConvertUtils;
import com.maimang.platform.lang.StringUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import tk.mybatis.mapper.entity.Example;

import java.util.*;

/**
 * @author Rocky
 * @version 1.0.0
 * @classname WorkExportServiceImpl
 * @description <br>工作统计导出service实现</br>
 * @date 2019-10-23 16:16
 * @email jintaozhao@qq.com
 */
@Service
public class WorkExportServiceImpl implements WorkExportService {

	public static final Logger log = LoggerFactory.getLogger(WorkExportServiceImpl.class);

	@Autowired
	private CrmWorkRecordMapper workRecordMapper;

	@Autowired
	private CrmCustomerMapper customerMapper;

	@Autowired
	private SysDeptMapper deptMapper;

	@Autowired
	private SysUserMapper userMapper;

	@Autowired
	private SysUserDeptMapper userDeptMapper;

	/**
	 * 工作内容统计
	 *
	 * @param deptId    部门id
	 * @param startTime 开始日期
	 * @param endTime   结束日期
	 * @return
	 */
	@Override
	public Map<String, List<Map<String, Object>>> workStatistics(Long deptId, String isNew, Date startTime, Date endTime) throws Exception {
		SysDept dept;
		String deptName = StringUtils.EMPTY, parentDeptName = StringUtils.EMPTY;
		List<SysUser> userList = new ArrayList<>();

		dept = deptMapper.selectDeptById(deptId);
		if (StringUtils.isNotNull(dept)) {
			deptName = dept.getDeptName();
			parentDeptName = dept.getParentName();

			SysUser user = new SysUser();
			user.setDeptId(dept.getDeptId());
			userList = userMapper.selectUserListDept(user);
		}

		// 全量数据，包括总量数据及个人统计数据
		Map<String, List<Map<String, Object>>> datas = new LinkedHashMap();

		// 总量数据
		List<Map<String, Object>> totalDataMap = new ArrayList<>();

		List<List<String>> dateList = DateForEachUtil.dateForEach(startTime, endTime, DateForEachUtil.WEEK);

		for (SysUser user : userList) {
			List<Map<String, Object>> userData = new ArrayList<>();

			CrmCustomer c = new CrmCustomer();
			c.setDeptName(deptName);
			c.setUserName(user.getUserName());
			if (StringUtils.isNotEmpty(isNew))
				c.setIsNew(isNew);
			List<CrmCustomer> customers = customerMapper.selectCrmCustomerList(c);

			// 处理电话/拜访事项
			for (int i = 0; i < customers.size(); i++) {
				Map<String, Object> data = new HashMap<>();
				// 基础数据
				data.put("parentDept", parentDeptName);
				data.put("deptName", deptName);
				data.put("leader", dept.getLeader());
				data.put("userName", user.getUserName());
				CrmCustomer customer = customers.get(i);
				data.put("customerName", customer.getCompanyName());
				data.put("isNew", customer.getIsNew());

				for (int m = 0; m < dateList.size(); m++) {
					int week = m + 1;
					List<String> dates = dateList.get(m);

					Date start = DateUtils.parseDate(dates.get(0));
					Date end = DateUtils.parseDate(dates.get(dates.size() - 1));

					// 电话记录
					List<CrmWorkRecord> callRecordList = this.getWorkRecordList(user.getUserName(), customer.getCustomerId(), 0, start, end);
					Map<String, List<CrmWorkRecord>> callMap = new HashMap<>();
					for (CrmWorkRecord crmWorkRecord : callRecordList) {
						callMap.computeIfAbsent(DateUtils.parseDateToStr(DateUtils.webFormat, crmWorkRecord.getStartTime()), k -> new ArrayList<>()).add(crmWorkRecord);
					}

					for (int j = 0; j < dates.size(); j++) {
						String dateStr = dates.get(j);
						String key = "call_" + dateStr;
						if (callMap.containsKey(dateStr)) {
							data.put(key, callMap.get(dateStr).size());
						} else {
							data.put(key, 0);
						}
					}
					// 周电话总数
					String callTotalKey = StringUtils.format("call_total_{}", week);
					data.put(callTotalKey, callRecordList.size());

					// 拜访记录
					List<CrmWorkRecord> visitRecordList = this.getWorkRecordList(user.getUserName(), customer.getCustomerId(), 1, startTime, endTime);
					Map<String, List<CrmWorkRecord>> visitMap = new HashMap<>();
					for (CrmWorkRecord crmWorkRecord : visitRecordList) {
						visitMap.computeIfAbsent(DateUtils.parseDateToStr(DateUtils.webFormat, crmWorkRecord.getStartTime()), k -> new ArrayList<>()).add(crmWorkRecord);
					}

					for (int j = 0; j < dates.size(); j++) {
						String dateStr = dates.get(j);
						String key = "visit_" + dateStr;
						if (visitMap.containsKey(dateStr)) {
							data.put(key, visitMap.get(dateStr).size());
						} else {
							data.put(key, 0);
						}
					}

					// 周拜访总数
					String visitTotalKey = StringUtils.format("visit_total_{}", week);
					data.put(visitTotalKey, visitRecordList.size());
				}

				// 处理多次拜访
				List<CrmWorkRecord> visitList = this.getWorkRecordList(user.getUserName(), customer.getCustomerId(), 1, startTime, endTime);
				if (visitList.size() >= 5) {
					data.put("visit5", 1);
				} else {
					data.put("visit" + visitList.size(), 1);
				}

				// 处理提案和签约
				// 提交方案
				List<CrmWorkRecord> solutionList = this.getWorkRecordList(user.getUserName(), customer.getCustomerId(), 2, startTime, endTime);
				int length = Math.max(5, solutionList.size());
				for (int j = 0; j < length; j++) {
					String soluKey = StringUtils.format("solution{}", j);
					if (j < solutionList.size()) {
						CrmWorkRecord solution = solutionList.get(j);
						data.put(soluKey, DateUtils.parseDateToStr(DateUtils.webFormat, solution.getStartTime()));
					} else {
						data.put(soluKey, StringUtils.EMPTY);
					}
				}
				// 提案累计次数
				String solutionTotal = "solution_total";
				data.put(solutionTotal, solutionList.size());

				// 签约
				List<CrmWorkRecord> signList = this.getWorkRecordList(user.getUserName(), customer.getCustomerId(), 3, startTime, endTime);
				if (signList.size() > 0) {
					int signNum = signList.size();
					data.put("sign_num", signNum);
					data.put("sign_money", signList);
					String signDate = DateUtils.parseDateToStr(DateUtils.webFormat, signList.get(signNum - 1).getStartTime());
					data.put("sign_date", signDate);
					data.put("sign_status", "");
				}
				userData.add(data);
				totalDataMap.add(data);
			}
			String userKey = PinyinUtil.getPinYin(user.getUserName());
			datas.put(userKey, userData);
		}
		// 全量汇总数据 放到第一页
		datas.put(PinyinUtil.getPinYin("全量汇总"), totalDataMap);
		return datas;
	}

	/**
	 * Excel统计报表数据
	 *
	 * @param sheets    sheet页数据对象
	 * @param deptId    部门ID
	 * @param startTime 开始时间
	 * @param endTime   结束时间
	 * @return
	 * @throws Exception
	 */
	@Override
	public Map<String, List<Map<String, Object>>> statistics(Map<String, String> sheets, Long deptId, Date startTime, Date endTime) throws Exception {
		Map<String, List<Map<String, Object>>> dataMap = new LinkedHashMap<>();

		for (String sheetKey : sheets.keySet()) {
			if (sheetKey.equalsIgnoreCase(SheetLabel.NEW) || sheetKey.equalsIgnoreCase(SheetLabel.OLD)) {
				List<Map<String, Object>> tableData = this.createTableData(sheetKey, deptId, startTime, endTime);
				dataMap.put(sheetKey, tableData);
			} else {
				List<Map<String, Object>> simpleTableData = this.createSimpleTableData(sheetKey, deptId, startTime, endTime);
				dataMap.put(sheetKey, simpleTableData);
			}
		}

		return dataMap;
	}

	/**
	 * 创建引领性指标表格数据（新客户/老客户）
	 *
	 * @param deptId    部门id
	 * @param startTime 开始时间
	 * @param endTime   结束时间
	 * @return
	 */
	private List<Map<String, Object>> createTableData(String sheetKey, Long deptId, Date startTime, Date endTime) throws Exception {
		String isNew = StringUtils.EMPTY;
		if (sheetKey.indexOf(SheetLabel.NEW) >= 0)
			isNew = "新客户";
		else
			isNew = "老客户";

		List<Map<String, Object>> tableData = new ArrayList<>();
		// 时间区间
		List<List<String>> dateList = DateForEachUtil.dateForEach(startTime, endTime, DateForEachUtil.WEEK);

		// 所选择的一级部门
		SysDept rootDept = deptMapper.selectDeptById(deptId);
		// 一级部门下属部门 KA一部二部等
		List<SysDeptTree> depts = this.selectChildren(deptId);

		// 遍历日期 构建电话和拜访表头数据
		for (int m = 0; m < dateList.size(); m++) {
			// 每个月的第几周
			int weekOfMonth = m + 1;
			// 这个周内所有日期
			List<String> daysOfWeek = dateList.get(m);

			String startDateStr = daysOfWeek.get(0);
			String endDateStr = daysOfWeek.get(daysOfWeek.size() - 1);

			for (SysDeptTree dept : depts) {
				handleData(tableData, dept, weekOfMonth, startDateStr, endDateStr, isNew);
			}
		}

		return tableData;
	}

	/**
	 * @param tableData    表格数据
	 * @param dept         部门
	 * @param weekOfMonth  本月多少周
	 * @param startDateStr 开始日期
	 * @param endDateStr   结束日期
	 * @param isNew        是否新客户
	 */
	private void handleData(List<Map<String, Object>> tableData, SysDeptTree dept, int weekOfMonth, String startDateStr, String endDateStr, String isNew) {
		List<SysUser> userList = this.getUserList(dept.getDeptId());
		for (SysUser user : userList) {
			Map userDataMap = createUserDataMap(dept, user, weekOfMonth, startDateStr, endDateStr, isNew);
			tableData.add(userDataMap);

			// 如果这个人是部门主管
			if (user.getIsLeaderInDepts().contains("true")) {
				Map<String, List<Map>> deptDataMap = new HashMap<>();
				for (Map dataMap : tableData) {
					deptDataMap.computeIfAbsent((String) dataMap.get("deptName"), k -> new ArrayList<>()).add(dataMap);
				}
				List<Map> result = deptDataMap.get(dept.getDeptName());
				Map<String, Object> res = new HashMap<>();
				result.stream().forEach(a -> a.keySet().forEach(b -> {
					if (!res.containsKey(b) && ConvertUtils.toFloat(a.get(b)) != null)
						res.put((String) b, 0);
					if (ConvertUtils.toFloat(a.get(b)) != null) {
						res.put((String) b, ConvertUtils.toFloat(res.get(b)) + ConvertUtils.toFloat(a.get(b)));
					}
				}));
				res.put("parentDept", dept.getParentName());
				SysDept sysDept = new SysDept();
				sysDept.setDeptId(dept.getDeptId());
				int count = deptMapper.selectDeptCount(sysDept);
				// int visitTotal = (int) res.get("visit_month");
				// float visitAvg = visitTotal / count;
				// res.put("visit_avg", visitAvg);
				tableData.add(res);
			}
		}
		if (dept.getChildren().size() > 0) {
			for (SysDeptTree deptTree : dept.getChildren()) {
				handleData(tableData, deptTree, weekOfMonth, startDateStr, endDateStr, isNew);
			}
		}
		return;
	}

	/**
	 * @param dept
	 * @param user
	 * @param weekOfMonth
	 * @param startDateStr
	 * @param endDateStr
	 * @param isNew
	 * @return
	 */
	private Map createUserDataMap(SysDeptTree dept, SysUser user, int weekOfMonth, String startDateStr, String endDateStr, String isNew) {
		// 基础数据
		Map<String, Object> userDataMap = new HashMap<>();
		userDataMap.put("parentDept", dept.getParentName());
		userDataMap.put("deptName", dept.getDeptName());
		if (user.getIsLeaderInDepts().contains("true")) {
			String name = StringUtils.format(user.getUserName() + "({})", user.getPosition());
			userDataMap.put("userName", user.getUserName());
		} else {
			userDataMap.put("userName", user.getUserName());
		}

		Date start = DateUtils.parseDate(startDateStr + " 00:00:00");
		Date end = DateUtils.parseDate(endDateStr + " 23:59:59");

		// 客户清单
		List<CrmCustomer> customers = this.getCustomerList(dept.getDeptId(), user.getUserName(), isNew);

		// 原始工作记录
		List<CrmWorkRecord> workRecordList = this.getWorkRecords(dept.getDeptId(), -1, isNew, start, end);

		// 工作记录
		Map<String, List<CrmWorkRecord>> workRecordMap = new HashMap<>();
		for (CrmWorkRecord workRecord : workRecordList) {
			String key = StringUtils.format("{}_{}", workRecord.getCustomerId(), workRecord.getWorkType());
			workRecordMap.computeIfAbsent(key, k -> new ArrayList<>()).add(workRecord);
		}
		for (Map.Entry<String, List<CrmWorkRecord>> workRecordEntry : workRecordMap.entrySet()) {
			String workKey = workRecordEntry.getKey();
			List<CrmWorkRecord> workList = workRecordEntry.getValue();
			if (workKey.indexOf("_1") >= 0) {
				int count = workList.size() <= 4 ? workList.size() : 4;
				String key = StringUtils.format("visit_{}_{}", weekOfMonth, count);
				if (userDataMap.containsKey(key)) {
					int preValue = (int) userDataMap.get(key);
					userDataMap.put(key, preValue + 1);
				} else {
					userDataMap.put(key, 1);
				}
				String weekKey = StringUtils.format("visit_{}_{}", weekOfMonth, 5);
				// 周拜访总数
				userDataMap.put(weekKey, count);
			} else if (workKey.indexOf("_2") >= 0) {
				int count = workList.size() <= 4 ? workList.size() : 4;
				String key = StringUtils.format("solution_{}_{}", weekOfMonth, count);
				if (userDataMap.containsKey(key)) {
					int preValue = (int) userDataMap.get(key);
					userDataMap.put(key, preValue + 1);
				} else {
					userDataMap.put(key, 1);
				}
			} else if (workKey.indexOf("_3") >= 0) {
				int count = workList.size() <= 4 ? workList.size() : 4;
				String key = StringUtils.format("sign_{}_{}", weekOfMonth, 1);
				if (userDataMap.containsKey(key)) {
					int preValue = (int) userDataMap.get(key);
					userDataMap.put(key, preValue + count);
				} else {
					userDataMap.put(key, count);
				}
			}
		}
		return userDataMap;
	}

	/**
	 * 下属部门
	 *
	 * @param deptId 部门ID
	 * @return
	 */
	private List<SysDeptTree> selectChildren(Long deptId) {
		List<SysDeptTree> deptTrees = deptMapper.selectChildren(deptId);
		deptTrees = TreeHelper.buildTree(deptTrees, deptId);
		return deptTrees;
	}

	/**
	 * 根据部门和用户获取客户清单
	 *
	 * @param deptId 部门ID
	 * @param userName 用户名
	 * @return
	 */
	private List<CrmCustomer> getCustomerList(Long deptId, String userName, String isNew) {
		CrmCustomer customer = new CrmCustomer();
		if (StringUtils.isNotNull(deptId))
			customer.setDeptId(deptId);
		if (StringUtils.isNotEmpty(userName))
			customer.setUserName(userName);
		if (StringUtils.isNotEmpty(isNew))
			customer.setIsNew(isNew);
		return customerMapper.selectCrmCustomerList(customer);
	}

	/**
	 * 获取工作记录
	 *
	 * @param deptId    部门id
	 * @param isNew     是否新客户
	 * @param startTime 开始时间
	 * @param endTime   结束时间
	 * @return
	 */
	private List<CrmWorkRecord> getWorkRecords(Long deptId, int workType, String isNew, Date startTime, Date endTime) {
		SysUser user = new SysUser();
		user.setDeptId(deptId);
		List<SysUser> userList = userMapper.selectUserList(user);
		List<Long> userIds = new ArrayList<>();
		for (SysUser sysUser : userList) {
			userIds.add(sysUser.getUserId());
		}

		Example example = new Example(CrmWorkRecord.class);
		Example.Criteria criteria = example.createCriteria();
		if (userIds.size() > 0)
			criteria.andIn("userId", userIds);

		if (workType != -1)
			criteria.andEqualTo("workType", workType);
		if (StringUtils.isNotEmpty(isNew))
			criteria.andEqualTo("isNew", isNew);
		if (StringUtils.isNotNull(startTime) && StringUtils.isNotNull(endTime))
			criteria.andBetween("startTime", startTime, endTime);

		List<CrmWorkRecord> recordList = workRecordMapper.selectByExample(example);
		return recordList;
	}

	/**
	 * 根据部门树获取用户列表
	 *
	 * @param deptId 部门ID
	 * @return
	 */
	private List<SysUser> getUserList(Long deptId) {
		if (deptId == null) return null;
		SysUser sysUser = new SysUser();
		sysUser.setDeptId(deptId);
		List<SysUser> userList = userMapper.selectUserList(sysUser);
		if (userList.size() == 0)
			return userList;
		userList.sort((x, y)->Integer.compare(x.getIsLeaderInDepts().indexOf("true"), y.getIsLeaderInDepts().indexOf("true")));
		return userList;
	}

	/**
	 * 创建引领性指标-简表表格数据（新客户/老客户）
	 *
	 * @param deptId    部门id
	 * @param startTime 开始时间
	 * @param endTime   结束时间
	 * @return
	 */
	private List<Map<String, Object>> createSimpleTableData(String sheetKey, Long deptId, Date startTime, Date endTime) throws Exception {
		List<Map<String, Object>> tableData = new ArrayList<>();

		String isNew = StringUtils.EMPTY;
		if (sheetKey.indexOf(SheetLabel.NEW) >= 0)
			isNew = "新客户";
		else
			isNew = "老客户";

		DateTime start = DateUtil.beginOfMonth(startTime);
		DateTime end = DateUtil.endOfMonth(startTime);

		// 部门
		SysDept dept = deptMapper.selectDeptById(deptId);

		List<List<String>> dateList = DateForEachUtil.dateForEach(start, end, DateForEachUtil.WEEK);

		String num[] = {"零", "一", "二", "三", "四", "五", "六", "七", "八", "九"};

		// 下属部门
		List<SysDeptTree> deptTreeList = this.selectChildren(deptId);
		for (SysDeptTree sysDept : deptTreeList) {
			Map deptDataMap = new LinkedHashMap();
			// 部门基础信息
			deptDataMap.put("parentDept", sysDept.getParentName());
			deptDataMap.put("deptName", sysDept.getDeptName());
			deptDataMap.put("leader", sysDept.getLeader());

			// 客户清单总数
			List<CrmCustomer> cusomers = this.getCustomerList(sysDept.getDeptId(), StringUtils.EMPTY, isNew);
			deptDataMap.put("total_num", cusomers.size());

			// 遍历日期 构建每周拜访数据
			for (int i = 0; i < dateList.size(); i++) {
				// 每个月的第几周
				int weekOfMonth = i + 1;
				String week = num[weekOfMonth];
				// 这个周内所有日期
				List<String> daysOfWeek = dateList.get(i);
				Date weekStart = DateUtils.parseDate(daysOfWeek.get(0) + " 00:00:00");
				Date weekEnd = DateUtils.parseDate(daysOfWeek.get(daysOfWeek.size() - 1) + " 23:59:59");

				List<CrmWorkRecord> recordList = this.getWorkRecords(sysDept.getDeptId(), 1, isNew, weekStart, weekEnd);
				String weekVisitId = StringUtils.format("visit_week_{}", weekOfMonth);

				// 工作记录
				Map<Integer, List<CrmWorkRecord>> workRecordMap = new HashMap<>();
				for (CrmWorkRecord workRecord : recordList) {
					workRecordMap.computeIfAbsent(workRecord.getCustomerId(), k -> new ArrayList<>()).add(workRecord);
				}
				int count = workRecordMap.values().size();
				deptDataMap.put(weekVisitId, count);
			}
			// 月度拜访客户总数
			List<CrmWorkRecord> monthRecordList = this.getWorkRecords(sysDept.getDeptId(), 1, isNew, start, end);
			Map<Integer, List<CrmWorkRecord>> monthWorkRecordMap = new HashMap<>();
			for (CrmWorkRecord workRecord : monthRecordList) {
				monthWorkRecordMap.computeIfAbsent(workRecord.getCustomerId(), k -> new ArrayList<>()).add(workRecord);
			}
			int count = monthWorkRecordMap.values().size();
			deptDataMap.put("visit_month", count);
			// 人均拜访客户数
			List<SysUser> users = getUserList(sysDept.getDeptId());
			if (users.size() == 0)
				deptDataMap.put("visit_avg", 0);
			else
				deptDataMap.put("visit_avg", ArithUtil.div(count, users.size(), 2));


			// 提案客户总数
			List<CrmWorkRecord> soluRecordList = this.getWorkRecords(sysDept.getDeptId(), 2, isNew, startTime, endTime);
			// 工作记录
			Map<Integer, List<CrmWorkRecord>> workRecordMap = new HashMap<>();
			for (CrmWorkRecord workRecord : soluRecordList) {
				workRecordMap.computeIfAbsent(workRecord.getCustomerId(), k -> new ArrayList<>()).add(workRecord);
			}
			int soluTotal = workRecordMap.values().size();
			String soluKey = "solution_total";
			deptDataMap.put(soluKey, soluTotal);

			// 签约客户总数
			List<CrmWorkRecord> signRecordList = this.getWorkRecords(sysDept.getDeptId(), 3, isNew, startTime, endTime);
			// 工作记录
			Map<Integer, List<CrmWorkRecord>> signRecordMap = new HashMap<>();
			for (CrmWorkRecord workRecord : signRecordList) {
				signRecordMap.computeIfAbsent(workRecord.getCustomerId(), k -> new ArrayList<>()).add(workRecord);
			}
			int countSign = signRecordMap.values().size();
			int month = DateUtil.month(startTime);
			// month月新客户签约客户数
			String signMonthId = StringUtils.format("sign_month_{}", month + 1);
			deptDataMap.put(signMonthId, countSign);

			tableData.add(deptDataMap);
		}

		// 计算部门合计
		Map<String, List<Map>> parentDataMap = new HashMap<>();
		for (Map dataMap : tableData) {
			parentDataMap.computeIfAbsent((String) dataMap.get("parentDept"), k -> new ArrayList<>()).add(dataMap);
		}
		List<Map> result = parentDataMap.get(dept.getDeptName());
		Map<String, Object> res = new HashMap<>();
		result.stream().forEach(a -> a.keySet().forEach(b -> {
			if (!res.containsKey(b) && ConvertUtils.toFloat(a.get(b)) != null)
				res.put((String) b, 0);
			if (ConvertUtils.toFloat(a.get(b)) != null) {
				res.put((String) b, ConvertUtils.toFloat(res.get(b)) + ConvertUtils.toFloat(a.get(b)));
			}
		}));
		res.put("parentDept", dept.getDeptName());
		res.put("leader", dept.getLeader());
		SysUser user = new SysUser();
		user.setDeptId(dept.getDeptId());
		List<SysUser> userListDept = userMapper.selectUserListDept(user);
		int count = userListDept.size();
		double visitTotal = Double.valueOf(res.get("visit_month").toString());
		double visitAvg = 0;
		if (count > 0)
			visitAvg = ArithUtil.div(visitTotal, count, 2);

		res.put("visit_avg", visitAvg);
		tableData.add(res);
		return tableData;
	}

	/**
	 * 获取工作内容详情
	 *
	 * @param userName   用户名
	 * @param customerId 客户id
	 * @param workType   工作类型
	 * @param startTime  开始时间
	 * @param endTime    结束时间
	 * @return
	 */
	private List<CrmWorkRecord> getWorkRecordList(String userName, Integer customerId, Integer workType, Date startTime, Date endTime) {
		CrmWorkRecord workRecord = new CrmWorkRecord();
		workRecord.setStartTime(startTime);
		workRecord.setEndTime(endTime);
		workRecord.setCustomerId(customerId);
		workRecord.setWorkType(workType);
		workRecord.setUserName(userName);

		List<CrmWorkRecord> workRecordList = workRecordMapper.selectCrmWorkRecordList(workRecord);

		return workRecordList;
	}

	/**
	 * 创建表格表头
	 *
	 * @param deptId    父部门名称
	 * @param startTime 开始时间
	 * @param endTime   结束时间
	 * @return
	 */
	@Override
	public List<TitleEntity> createTitleList(Long deptId, Date startTime, Date endTime) throws Exception {
		//key-Integer为周，value-List<String>每个周包含的日期
		List<List<String>> dateList  = DateForEachUtil.dateForEach(startTime, endTime, DateForEachUtil.WEEK);

		SysDept dept = deptMapper.selectDeptById(deptId);
		List<TitleEntity> columns = getFixedColumn(dept.getDeptName());

		String num[] = {"零", "一", "二", "三", "四", "五", "六", "七", "八", "九"};


		// 遍历日期 构建电话和拜访表头数据
		for (int m = 0; m < dateList.size(); m++) {
			// 每个月的第几周
			int weekOfMonth = m + 1;

			String week = num[weekOfMonth];
			// 这个周内所有日期
			List<String> daysOfWeek = dateList.get(m);
			// 电话
			String titleId0 = StringUtils.format("call_{}", weekOfMonth);
			TitleEntity titleEntity0 = new TitleEntity(titleId0, "0", StringUtils.format("第{}周电话", week), null);
			columns.add(titleEntity0);
			for (int i = 0; i < daysOfWeek.size(); i++) {
				String dateStr = daysOfWeek.get(i);
				String callTitleId = "call_" + dateStr;
				TitleEntity callTitle = new TitleEntity(callTitleId, titleId0, dateStr, callTitleId);
				columns.add(callTitle);
			}
			// 周电话总数
			String titleId_total_0 = StringUtils.format("call_total_{}", weekOfMonth);
			TitleEntity titleCallTotal = new TitleEntity(titleId_total_0, titleId0, "周电话总数", titleId_total_0);
			columns.add(titleCallTotal);

			// 拜访
			String titleId1 = StringUtils.format("visit_{}", weekOfMonth);
			TitleEntity titleEntity1 = new TitleEntity(titleId1, "0", StringUtils.format("第{}周拜访", week), null);
			columns.add(titleEntity1);
			for (int i = 0; i < daysOfWeek.size(); i++) {
				String dateStr = daysOfWeek.get(i);
				String visitTitleId = "visit_" + dateStr;
				TitleEntity visitTitle = new TitleEntity(visitTitleId, titleId1, dateStr, visitTitleId);
				columns.add(visitTitle);
			}
			// 周拜访总数
			String titleId_total_1 = StringUtils.format("visit_total_{}", weekOfMonth);
			TitleEntity titleVisitTotal = new TitleEntity(titleId_total_1, titleId1, "周拜访总数", titleId_total_0);
			columns.add(titleVisitTotal);
		}

		// 多次拜访
		String titleId1 = StringUtils.format("visit_{}", 0);
		TitleEntity titleEntity1 = new TitleEntity(titleId1, "0", "多次拜访次数", null);
		columns.add(titleEntity1);
		for (int i = 1; i <= 4; i++) {
			String visitTitleId = "visit" + i;
			String content = StringUtils.format("{}次拜访", num[i]);
			if (i == 4)
				content = "四次及以上拜访";
			TitleEntity solutionTitle = new TitleEntity(visitTitleId, titleId1, content, visitTitleId);
			columns.add(solutionTitle);
		}

		// 构建提交方案和合同签约表头数据
		// 提案
		String titleId2 = StringUtils.format("solution_{}", 0);
		TitleEntity titleEntity2 = new TitleEntity(titleId2, "0", "提报方案次数", null);
		columns.add(titleEntity2);
		for (int i = 1; i <= 5; i++) {
			String solutionTitleId = "solution" + i;
			String content = StringUtils.format("{}次提案日期", num[i]);
			TitleEntity solutionTitle = new TitleEntity(solutionTitleId, titleId2, content, solutionTitleId);
			columns.add(solutionTitle);
		}

		String titleSolutionId = "solution_total";
		TitleEntity titleEntitySolution = new TitleEntity(titleSolutionId, titleId2, "提案累计次数", titleSolutionId);
		columns.add(titleEntitySolution);

		// 签约
		String titleId3 = "sign";
		TitleEntity titleEntity3 = new TitleEntity(titleId3, "0", "签约合同情况", null);
		columns.add(titleEntity3);
		// 签约合同数量
		String signNumId = "sign_num";
		TitleEntity signNumTitle = new TitleEntity(signNumId, titleId3, "合同数量", signNumId);
		columns.add(signNumTitle);
		// 签约合同金额
		String signMoneyId = "sign_money";
		TitleEntity signMoneyTitle = new TitleEntity(signMoneyId, titleId3, "签约合同金额", signMoneyId);
		columns.add(signMoneyTitle);
		// 签约合同日期
		String signDateId = "sign_date";
		TitleEntity signDateTitle = new TitleEntity(signDateId, titleId3, "签约日期(老客户为最后一次合同日期)", signDateId);
		columns.add(signDateTitle);

		// 签约合同日期
		String signStatusId = "sign_status";
		TitleEntity signStatusTitle = new TitleEntity(signStatusId, titleId3, "签约状态", signDateId);
		columns.add(signStatusTitle);

		return columns;
	}

	/**
	 * 创建多个表头，每个sheet页不同
	 *
	 * @param sheets    sheet页信息
	 * @param startTime 起始时间
	 * @param endTime   结束时间
	 * @return
	 * @throws Exception
	 */
	@Override
	public Map<String, List<TitleEntity>> createTitles(Map<String, String> sheets, Date startTime, Date endTime) throws Exception {
		Map<String, List<TitleEntity>> titleListMap = new LinkedHashMap<>();

		for (String sheetKey : sheets.keySet()) {
			List<TitleEntity> titleEntityList = this.createSheetTitles(sheetKey, startTime, endTime);
			titleListMap.put(sheetKey, titleEntityList);
		}
		return titleListMap;
	}

	/**
	 * 根据不同sheet页key获取不同的表头对象列表
	 *
	 * @param sheetTypeKey sheet页key值
	 * @param startTime    开始时间
	 * @param endTime      结束时间
	 * @return
	 * @throws Exception
	 */
	private List<TitleEntity> createSheetTitles(String sheetTypeKey, Date startTime, Date endTime) throws Exception {
		//key-Integer为周，value-List<String>每个周包含的日期
		List<List<String>> dateList = DateForEachUtil.dateForEach(startTime, endTime, DateForEachUtil.WEEK);

		List<TitleEntity> columns = this.getBaseColumns(sheetTypeKey);

		String num[] = {"零", "一", "二", "三", "四", "五", "六", "七", "八", "九"};

		if (sheetTypeKey.equalsIgnoreCase(SheetLabel.NEW) || sheetTypeKey.equalsIgnoreCase(SheetLabel.OLD)) {
			// 遍历日期 构建电话和拜访表头数据
			for (int m = 0; m < dateList.size(); m++) {
				// 每个月的第几周
				int weekOfMonth = m + 1;
				String week = num[weekOfMonth];
				// 这个周内所有日期
				List<String> daysOfWeek = dateList.get(m);
				// 标题 如：10月第一周（10.8-10.11）
				String startDateStr = daysOfWeek.get(0);
				String endDateStr = daysOfWeek.get(daysOfWeek.size() - 1);
				String monthStr = startDateStr.substring(startDateStr.indexOf("-") + 1, startDateStr.lastIndexOf("-"));
				String content = StringUtils.format("{}月第{}周（{}-{}）", monthStr, weekOfMonth, startDateStr.substring(startDateStr.indexOf("-") + 1), endDateStr.substring(endDateStr.indexOf("-")));
				String titleId = StringUtils.format("week_{}", weekOfMonth);
				TitleEntity titleEntity0 = new TitleEntity(titleId, "0", content, null);
				columns.add(titleEntity0);

				// 客户清单总数
				String totalNumId = StringUtils.format("total_num_{}", weekOfMonth);
				TitleEntity totalCustomerNum = new TitleEntity(totalNumId, titleId, "清单客户总数", totalNumId);
				columns.add(totalCustomerNum);

				// 客户拜访次数
				String visitId = StringUtils.format("visit_week_{}", weekOfMonth);
				TitleEntity visitWeekTitle = new TitleEntity(visitId, titleId, "客户拜访次数", null);
				columns.add(visitWeekTitle);
				// 一次拜访,二次拜访,三次拜访,四次及以上拜访,周累计拜访客户数,月累计拜访客户数,人均拜访量
				for (int i = 1; i <= 7; i++) {
					String childVisitId = StringUtils.format("visit_{}_{}", weekOfMonth, i);
					String visitContent = StringUtils.format("{}次拜访", num[i]);
					if (i == 4) visitContent = "四次及以上拜访";
					if (i == 5) visitContent = "周累计拜访客户数";
					if (i == 6) visitContent = "月累计拜访客户数";
					if (i == 7) visitContent = "人均拜访量";
					TitleEntity visitTitle = new TitleEntity(childVisitId, visitId, visitContent, childVisitId);
					columns.add(visitTitle);
				}

				// 提报方案次数
				String solutionId = StringUtils.format("solution_week_{}", weekOfMonth);
				TitleEntity soluWeekTitle = new TitleEntity(solutionId, titleId, "提报方案次数", null);
				columns.add(soluWeekTitle);

				// 一次提案,二次提案,三次提案,三次以上提案
				for (int i = 1; i <= 4; i++) {
					String childSoluId = StringUtils.format("solution_{}_{}", weekOfMonth, i);
					String soluContent = StringUtils.format("{}次提案", num[i]);
					if (i == 4) soluContent = "三次以上提案";
					TitleEntity soluTitle = new TitleEntity(childSoluId, visitId, soluContent, childSoluId);
					columns.add(soluTitle);
				}

				// 签约情况
				String signId = StringUtils.format("sign_week_{}", weekOfMonth);
				TitleEntity signWeekTitle = new TitleEntity(signId, titleId, "签约情况", null);
				columns.add(signWeekTitle);
				// 签约次数	月度签约总额
				for (int i = 1; i <= 2; i++) {
					String childSignId = StringUtils.format("sign_{}_{}", weekOfMonth, i);
					String signContent = "签约次数";
					if (i == 2) signContent = "月度签约总额";
					TitleEntity signTitle = new TitleEntity(childSignId, signId, signContent, childSignId);
					columns.add(signTitle);
				}
			}
		} else {
			// 遍历日期 构建每月各周拜访表头数据
			DateTime begin = DateUtil.beginOfMonth(startTime);
			DateTime end = DateUtil.endOfMonth(endTime);
			List<List<String>> dateMonthMap = DateForEachUtil.dateForEach(begin, end, DateForEachUtil.WEEK);

			// 客户清单总数
			String totalNumId = "total_num";
			TitleEntity totalCustomerNum = new TitleEntity(totalNumId, "0", "清单客户总数", totalNumId);
			columns.add(totalCustomerNum);

			// 客户拜访
			String visitId = "visit_week";
			TitleEntity visitWeekTitle = new TitleEntity(visitId, "0", "客户拜访", null);
			columns.add(visitWeekTitle);

			for (int i = 0; i < dateList.size(); i++) {
				String weekVisitId = StringUtils.format("visit_week_{}", i + 1);
				String visitContent = StringUtils.format("第{}周拜访客户数", num[i + 1]);
				TitleEntity visitTitle = new TitleEntity(weekVisitId, visitId, visitContent, weekVisitId);
				columns.add(visitTitle);
			}
			// 月度拜访客户总数
			String monthVisitId = "visit_month";
			String monthVisitContent = "月度拜访客户总数";
			TitleEntity monthVisitTitle = new TitleEntity(monthVisitId, visitId, monthVisitContent, monthVisitId);
			columns.add(monthVisitTitle);

			// 人均拜访客户数
			String avgVisitId = "visit_avg";
			String avgVisitContent = "人均拜访客户数";
			TitleEntity avgVisitTitle = new TitleEntity(avgVisitId, visitId, avgVisitContent, avgVisitId);
			columns.add(avgVisitTitle);

			// 提案客户总数
			String soluTotalId = "solution_total";
			TitleEntity soluTotalTitle = new TitleEntity(soluTotalId, "0", "提案客户总数", soluTotalId);
			columns.add(soluTotalTitle);

			// 签约情况
			// 品牌新客户
			int month = DateUtil.month(startTime);
			if (sheetTypeKey.equalsIgnoreCase(SheetLabel.NEW_SIMPLE)) {
				// month月新客户签约客户数
				String signMonthId = StringUtils.format("sign_month_{}", month + 1);
				TitleEntity signMonthTitle = new TitleEntity(signMonthId, "0", StringUtils.format("{}月新客户签约客户数", month + 1), signMonthId);
				columns.add(signMonthTitle);
				// month月新客户签约客户总额
				String signMoneyId = StringUtils.format("sign_money_{}", month + 1);
				TitleEntity signMoneyTitle = new TitleEntity(signMoneyId, "0", StringUtils.format("{}新客户签约客户总额", month + 1), signMoneyId);
				columns.add(signMoneyTitle);
			} else {// 品牌老客户
				// 签约次数
				String signMonthId = StringUtils.format("sign_month_{}", month + 1);
				TitleEntity signCountTitle = new TitleEntity(signMonthId, "0", "签约次数", signMonthId);
				columns.add(signCountTitle);
				// 季度签约总额
				String signQuarterId = "sign_quarter";
				TitleEntity signQuarterTitle = new TitleEntity(signQuarterId, "0", "季度签约总额", signQuarterId);
				columns.add(signQuarterTitle);
				// 合同个数
				String signNumId = "sign_num";
				TitleEntity signNumTitle = new TitleEntity(signNumId, "0", "合同个数", signNumId);
				columns.add(signNumTitle);
				// 年度合同
				String signYearId = "sign_year";
				TitleEntity signYearTitle = new TitleEntity(signYearId, "0", "年度合同总额", signYearId);
				columns.add(signYearTitle);
			}
		}

		return columns;
	}

	/**
	 * 获取所有的sheet名称
	 *
	 * @param deptId
	 * @return sheet页列表
	 */
	@Override
	public Map<String, String> getSheetNames(Long deptId) {
		Map<String, String> sheetNameMap = new LinkedHashMap<>();
		String firstSheet = "全量汇总";
		sheetNameMap.put(PinyinUtil.getPinYin(firstSheet), firstSheet);

		List<SysUser> userList = new ArrayList<>();

		SysDept dept = deptMapper.selectDeptById(deptId);
		if (StringUtils.isNotNull(dept)) {
			SysUser user = new SysUser();
			user.setDeptId(dept.getDeptId());
			userList = userMapper.selectUserList(user);
			for (SysUser sysUser : userList) {
				String userName = sysUser.getUserName();
				String key = PinyinUtil.getPinYin(userName);
				sheetNameMap.put(key, userName);
			}
		}
		return sheetNameMap;
	}

	/**
	 * 创建sheet页名称列表
	 *
	 * @return sheet页名称列表
	 */
	@Override
	public Map<String, String> createSheetNames() {
		Map<String, String> sheetMap = new LinkedHashMap<>();
		// sheetMap.put(SheetLabel.NEW, "引领性指标-新客户");
		// sheetMap.put(SheetLabel.OLD, "引领性指标-老客户");
		sheetMap.put(SheetLabel.NEW_SIMPLE, "引领性指标-新客户-简表");
		sheetMap.put(SheetLabel.OLD_SIMPLE, "引领性指标-老客户-简表");
		return sheetMap;
	}


	private Map getWorkType() {
		Map<Integer, String> workTypeMap = new HashMap<>();
		workTypeMap.put(0, "电话");
		workTypeMap.put(1, "拜访");
		workTypeMap.put(2, "提案");
		workTypeMap.put(3, "签约");
		return workTypeMap;
	}

	/**
	 * 获取固定列表头
	 *
	 * @param parentDeptName 部门名称
	 * @return
	 */
	private List<TitleEntity> getFixedColumn(String parentDeptName) {
		List<TitleEntity> titleList = new ArrayList<>();

		TitleEntity titleEntity0 = new TitleEntity("0", null, StringUtils.format("引领性指标报表-- {}客户", parentDeptName), null);
		TitleEntity titleEntity = new TitleEntity("1", "0", "基本信息", null);
		TitleEntity titleEntity1 = new TitleEntity("1_1", "1", "部门", "parentDept");
		TitleEntity titleEntity2 = new TitleEntity("1_2", "1", "小组", "deptName");
		TitleEntity titleEntity3 = new TitleEntity("1_3", "1", "销售主管", "leader");
		TitleEntity titleEntity4 = new TitleEntity("1_4", "1", "销售", "userName");
		TitleEntity titleEntity5 = new TitleEntity("1_5", "1", "客户名称", "customerName");
		TitleEntity titleEntity6 = new TitleEntity("1_6", "1", "是否新客户", "isNew");

		titleList.add(titleEntity0);
		titleList.add(titleEntity);
		titleList.add(titleEntity1);
		titleList.add(titleEntity2);
		titleList.add(titleEntity3);
		titleList.add(titleEntity4);
		titleList.add(titleEntity5);
		titleList.add(titleEntity6);
		return titleList;
	}

	/**
	 * 获取固定列表头
	 *
	 * @param sheetType sheet页报表类型
	 * @return
	 */
	private List<TitleEntity> getBaseColumns(String sheetType) {
		List<TitleEntity> titleList = new ArrayList<>();

		String titleName = "引领性指标报表-- 品牌{}客户";
		switch (sheetType) {
			case SheetLabel.NEW:
			case SheetLabel.NEW_SIMPLE:
				titleName = StringUtils.format(titleName, "新");
				break;
			case SheetLabel.OLD:
			case SheetLabel.OLD_SIMPLE:
				titleName = StringUtils.format(titleName, "老");
				break;
			default:
				titleName = StringUtils.format(titleName, StringUtils.EMPTY);
				break;
		}

		TitleEntity titleEntity0 = new TitleEntity("0", null, titleName, null);
		titleList.add(titleEntity0);

		TitleEntity titleEntity1 = new TitleEntity("1_1", "0", "部门", "parentDept");
		titleList.add(titleEntity1);

		TitleEntity titleEntity2 = new TitleEntity("1_2", "0", "小组", "deptName");
		titleList.add(titleEntity2);

		if (sheetType.equalsIgnoreCase(SheetLabel.NEW) || sheetType.equalsIgnoreCase(SheetLabel.OLD)) {
			TitleEntity titleEntity3 = new TitleEntity("1_3", "0", "销售/负责人", "userName");
			titleList.add(titleEntity3);
		} else {
			TitleEntity titleEntity3 = new TitleEntity("1_3", "0", "负责人", "leader");
			titleList.add(titleEntity3);
		}

		return titleList;
	}
}
